'use strict';

const Service = require('egg').Service;
const Sequelize = require('sequelize');
const Op = Sequelize.Op;

class MusicService extends Service {
  // 歌曲列表
  async page(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0
      },
      order:[
        ['createTime', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
              )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','lyrics'],
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ],
      distinct: true
    };
    if(query.name){
      config.where.name = {
        [Op.like]:`%${query.name}%`
      }
    }
    if(query.sid){
      config.where.sid = query.sid;
    }
    if(query.aid){
      config.where.aid = query.aid;
    }
    if(query.cid){
      config.where.cid = query.cid;
    }
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Music.findAndCountAll(config);
  }

  // 歌曲详情
  async detail(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id
      },
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
              )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime']
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    return await ctx.model.Music.findOne(config);
  }

  // 歌曲添加
  async add(data) {
    const { ctx } = this;
    return await ctx.model.Music.create({
      name:data.name,
      desc:data.desc?data.desc:'',
      cover:data.cover?data.cover:'',
      music:data.music,
      cid:data.cid,
      sid:data.sid?data.sid:null,
      aid:data.aid?data.aid:null,
      lyrics:data.lyrics?JSON.stringify(data.lyrics):'',
      status:data.status,
      createTime:new Date().getTime(),
      updateTime:new Date().getTime(),
      delTime:0
    });
  }

  // 歌曲编辑
  async edit(data) {
    const { ctx } = this;
    return await ctx.model.Music.update({
      name:data.name,
      desc:data.desc?data.desc:'',
      cover:data.cover?data.cover:'',
      music:data.music,
      cid:data.cid,
      sid:data.sid?data.sid:null,
      aid:data.aid?data.aid:null,
      lyrics:data.lyrics?data.lyrics:'',
      status:data.status,
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id
      }
    });
  }

  // 歌曲删除
  async del(data) {
    const { ctx } = this;
    const t = await ctx.model.transaction();
    try{
      let [collectionDel,likeDel,commentDel,musicDel] = await Promise.all(
        [
          // 删除收藏
          ctx.model.Collection.destroy({
            where: {
              type:1,
              cid:data.id
            },
            transaction:t
          }),
          // 删除点赞
          ctx.model.Likes.destroy({
            where: {
              type:1,
              lid:data.id
            },
            transaction:t
          }),
          // 删除评论
          ctx.model.Comment.update({
            isDel:1,
            delTime:new Date().getTime()
          },{
            where:{
              type:1,
              cid:data.id
            },
            transaction:t
          }),
          // 删除歌曲
          ctx.model.Music.update({
            isDel:1,
            delTime:new Date().getTime()
          },{
            where:{
              id:data.id
            },
            transaction:t
          })
        ]
      );
      if(!musicDel[0]){
        throw new Error('error');
      }
      await t.commit();
      return musicDel;
    }catch(e){
      await t.rollback();
      return [0];
    }
  }

  // 歌曲修改状态
  async status(data) {
    const { ctx } = this;
    return await ctx.model.Music.update({
      status:data.status,
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id
      }
    });
  }

  // 添加/编辑歌曲获取分类列表
  async classifyList(query) {
    const { ctx } = this;
    if(ctx.pageNO){
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      if(!query.pageSize){
        query.pageSize = 10;
      }
      config.offset = (query.pageNO-1)*query.pageSize;
      config.limit = query.pageSize;
      return await ctx.model.Classify.findAndCountAll(config);
    }else{
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      return await ctx.model.Classify.findAll(config);
    }
  }

  // 添加/编辑歌曲获取歌星列表
  async starList(query) {
    const { ctx } = this;
    if(ctx.pageNO){
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      if(!query.pageSize){
        query.pageSize = 10;
      }
      config.offset = (query.pageNO-1)*query.pageSize;
      config.limit = query.pageSize;
      return await ctx.model.Star.findAndCountAll(config);
    }else{
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      return await ctx.model.Star.findAll(config);
    }
  }

  // 添加/编辑歌曲获取专辑列表
  async albumList(query) {
    const { ctx } = this;
    if(ctx.pageNO){
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      if(!query.pageSize){
        query.pageSize = 10;
      }
      config.offset = (query.pageNO-1)*query.pageSize;
      config.limit = query.pageSize;
      return await ctx.model.Album.findAndCountAll(config);
    }else{
      let config = {
        where:{
          isDel:0
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: ['id', 'name', 'desc']
      };
      if(query.name){
        config.where.name = {
          [Op.like]:`%${query.name}%`
        }
      }
      return await ctx.model.Album.findAll(config);
    }
  }

  // 递归获取评论集合
  async recursiveComment(arr,id){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        pid:id,
        type:1
      },
      attributes: ['id']
    };
    let result = await ctx.model.Comment.findOne(config);
    if(result){
      arr.push(result.id);
      await this.recursiveComment(arr,result.id);
    }
  }

  // 歌曲评论列表
  async commentPage(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        cid:query.cid,
        type:1
      },
      order:[
        ['createTime', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                      SELECT COUNT(*)
                      FROM likes
                      WHERE
                          likes.lid = comment.id
                          AND
                          likes.type = 5
                  )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                      SELECT COUNT(*)
                      FROM comment AS c
                      WHERE
                          c.pid = comment.id
                          AND
                          c.type = 1
                          AND
                          c.isDel = 0
                  )`),
            'commentNum'
          ],
          [Sequelize.col('user.account'), 'account'],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','type'],
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }],
      distinct: true
    };
    config.where.pid = query.pid?query.pid:0;
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Comment.findAndCountAll(config);
  }

  // 歌曲评论详情
  async commentDetail(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id,
        type:1
      },
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                      SELECT COUNT(*)
                      FROM likes
                      WHERE
                          likes.lid = comment.id
                          AND
                          likes.type = 5
                  )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                      SELECT COUNT(*)
                      FROM comment AS c
                      WHERE
                          c.pid = comment.id
                          AND
                          comment.type = 1
                          AND
                          comment.isDel = 0
                  )`),
            'commentNum'
          ],
          [Sequelize.col('user.account'), 'account'],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','type']
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }]
    };
    return await ctx.model.Comment.findOne(config);
  }

  // 歌曲评论删除
  async commentDel(data) {
    const { ctx } = this;
    const t = await ctx.model.transaction();
    try{
      let delArr = [data.id];
      await this.recursiveComment(delArr,data.id);
      let [likeDel,commentDel] = await Promise.all(
        [
          // 删除点赞
          ctx.model.Likes.destroy({
            where: {
              type:5,
              lid:{
                [Op.in]:delArr
              }
            },
            transaction:t
          }),
          // 删除评论
          ctx.model.Comment.update({
            isDel:1,
            delTime:new Date().getTime()
          },{
            where:{
              type:1,
              id:{
                [Op.in]:delArr
              }
            },
            transaction:t
          }),
        ]
      );
      if(!commentDel[0]){
        throw new Error('error');
      }
      await t.commit();
      return commentDel;
    }catch(e){
      await t.rollback();
      return [0];
    }
  }

  // 歌曲评论状态修改
  async commentStatus(data) {
    const { ctx } = this;
    return await ctx.model.Comment.update({
      status:data.status,
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id,
        type:1
      }
    });
  }

  // 获取歌曲总数
  async getNum() {
    const { ctx } = this;
    return await ctx.model.Music.findOne({
      attributes: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.isDel = 0
              )`),
            'num'
          ]
        ]
    });
  }

  // 播放量排名前十歌曲
  async getTopMusic(){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0
      },
      offset:0,
      limit:10,
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
              )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','lyrics'],
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    return await ctx.model.Music.findAll(config);
  }
}
module.exports = MusicService;
